package edu.rit.hvz.android.DataLayer;

import java.util.ArrayList;
import java.util.List;

import org.apache.http.NameValuePair;
import org.apache.http.message.BasicNameValuePair;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteConstraintException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MissionDBAdapter {
    public static final String ROW_ID = "_id";
    public static final String TITLE = "title";
    public static final String CONTENT = "content";
    public static final String VISIBLE = "visible";

    private static final String DATABASE_TABLE = "mission";

    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;

    private final Context mCtx;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        DatabaseHelper(Context context) {
            super(context, MainDBAdapter.DATABASE_NAME, null, MainDBAdapter.DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx
     *            the Context within which to work
     */
    public MissionDBAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open the Missions database. If it cannot be opened, try to create a new
     * instance of the database. If it cannot be created, throw an exception to
     * signal the failure
     * 
     * @return this (self reference, allowing this to be chained in an
     *         initialization call)
     * @throws SQLException
     *             if the database could be neither opened or created
     */
    public MissionDBAdapter open() throws SQLException {
        this.mDbHelper = new DatabaseHelper(this.mCtx);
        this.mDb = this.mDbHelper.getWritableDatabase();
        return this;
    }

    /**
     * close return type: void
     */
    public void close() {
        this.mDbHelper.close();
    }


    public long createMission(Long id, String title, String content, boolean visible){
        ContentValues initialValues = new ContentValues();
        initialValues.put(ROW_ID, id);
        initialValues.put(TITLE, title);
        initialValues.put(CONTENT, content);
        initialValues.put(VISIBLE, visible);
        long result = -1;
        try {
        result = this.mDb.insertOrThrow(DATABASE_TABLE, null, initialValues);
        
        } catch (SQLiteConstraintException e) {    
        	// The record already exists in the database, update it for changes
        	this.updateMissionLocal(id, title, content, visible);
        }
        
        return result;
    }
    
    public long createMissionServer(String title, String content, boolean visible){
    	List<NameValuePair> data = new ArrayList<NameValuePair>();
		data.add(new BasicNameValuePair("title", title));
		data.add(new BasicNameValuePair("content", content));
		data.add(new BasicNameValuePair("HorZ", "H"));
		data.add(new BasicNameValuePair("visable", Integer.toString(visible ? 1 : 0)));
		
		String result = ServerDatabaseConnection.executePHPPost("http://vm444-2.se.rit.edu/CreateMission.php", data);
		try{
			return Long.parseLong(result);
		} catch(NumberFormatException e ){
			return -1;
		}
    }

    /**
     * Delete the Mission with the given rowId
     * 
     * @param rowId
     * @return true if deleted, false otherwise
     */
    public boolean deleteMissionLocal(long rowId) {

        return this.mDb.delete(DATABASE_TABLE, ROW_ID + "=" + rowId, null) > 0; //$NON-NLS-1$
    }
    
    public void deleteMissionServer(long rowId){
    	List<NameValuePair> data = new ArrayList<NameValuePair>();
		data.add(new BasicNameValuePair("missionID", Long.toString(rowId)));
		
		String result = ServerDatabaseConnection.executePHPPost("http://vm444-2.se.rit.edu/DeleteMission.php", data);
		
		deleteMissionLocal(rowId);
    }
    
    public void deleteAll(){
    	mDb.delete(DATABASE_TABLE, null, null);
    }

    /**
     * Return a Cursor over the list of all Missions in the database
     * 
     * @return Cursor over all Missions
     */
    public Cursor getAllMissions() {

    	String query = 
    			"select " + DATABASE_TABLE +"."+ROW_ID+", "+
    					DATABASE_TABLE +"."+TITLE+", "+
    					DATABASE_TABLE +"."+CONTENT+", "+
    					DATABASE_TABLE +"."+VISIBLE+" "+
    					"from " + DATABASE_TABLE;    					

        return this.mDb.rawQuery(query, null);
    }

    /**
     * Return a Cursor positioned at the Mission that matches the given rowId
     * @param rowId
     * @return Cursor positioned to matching Mission, if found
     * @throws SQLException if Mission could not be found/retrieved
     */
    public Cursor getMission(long rowId) throws SQLException {
    	String query = 
    			"select " + DATABASE_TABLE +"."+ROW_ID+", "+
    					DATABASE_TABLE +"."+TITLE+", "+
    					DATABASE_TABLE +"."+CONTENT+", "+
    					DATABASE_TABLE +"."+VISIBLE+" "+
    					"FROM " + DATABASE_TABLE + " " +
    					"WHERE " + DATABASE_TABLE +"."+ROW_ID+"=?";
    	   	
        Cursor mCursor =

        this.mDb.rawQuery(query, new String[]{Long.toString(rowId)});
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        
        return mCursor;        
       
    }
    
    public Cursor filterMissionByName(String constraint) {
    	String query = 
    			"select " + DATABASE_TABLE +"."+ROW_ID+", "+
    					DATABASE_TABLE +"."+TITLE+", "+
    					DATABASE_TABLE +"."+CONTENT+", "+
    					DATABASE_TABLE +"."+VISIBLE+" "+
    					"FROM " + DATABASE_TABLE + " " +
    					"WHERE " + DATABASE_TABLE +"."+TITLE+" like ? " +
    					"OR " + DATABASE_TABLE +"."+CONTENT+" like ? ";
    	
    	Cursor mCursor =

    	        this.mDb.rawQuery(query, new String[] {"%" + constraint + "%", "%" + constraint + "%"});
    	        if (mCursor != null) {
    	            mCursor.moveToFirst();
    	        }
    	        return mCursor;
    }
      

  
    private boolean updateMissionLocal(long rowId, String title, String content,
            boolean visible){
        ContentValues args = new ContentValues();
        args.put(TITLE, title);
        args.put(CONTENT, content);
        args.put(VISIBLE, visible);

        return this.mDb.update(DATABASE_TABLE, args, ROW_ID + "=" + rowId, null) >0; 
    }
    
    public void updateMissionServer( long rowId, String title, String content, boolean visible ){
		List<NameValuePair> data = new ArrayList<NameValuePair>();
		data.add(new BasicNameValuePair("missionID", Long.toString(rowId)));
		data.add(new BasicNameValuePair("title", title));
		data.add(new BasicNameValuePair("content", content));
		data.add(new BasicNameValuePair("who", "H"));
		data.add(new BasicNameValuePair("visable", Integer.toString(visible ? 1 : 0)));
		
		ServerDatabaseConnection.executePHPPost("http://vm444-2.se.rit.edu/EditMission.php", data);
		
		updateMissionLocal(rowId, title, content, visible);				
    }

}
